package com.csi.dao;

import com.csi.model.Inform;
import com.csi.util.JdbcUtil;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sound.sampled.Line.Info;

/**
 *
 * @author sunran
 * @date 9:58 2019/10/25
 */
public class InformDao {

  public void addInform(Inform inform){
    Connection conn = JdbcUtil.getConn();

    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement("insert into inform_inf(`title`,`content`,`gmt_created`,`creator_id`) values (?,?,now(3),?)");
      stmt.setString(1, inform.getTitle());
      stmt.setString(2, inform.getContent());
      stmt.setInt(3, inform.getCreatorId());

      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtil.closeConn(null, stmt, conn);
    }
  }


  public void delete(int[] ids) {
    Connection conn = JdbcUtil.getConn();

    StringBuffer temp = new StringBuffer("(");
    for (int i=0; i < ids.length; i++) {
      temp.append(ids[i]);
      if (i != ids.length - 1) {
        temp.append(",");
      }
    }
    temp.append(")");
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement("delete from inform_inf where id in "+temp.toString());

      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtil.closeConn(null, stmt, conn);
    }
  }

  public void update(Inform inform) {
    Connection conn = JdbcUtil.getConn();

    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement("update  inform_inf set title=?,content=? where id = ?");
      stmt.setString(1, inform.getTitle());
      stmt.setString(2, inform.getContent());
      stmt.setInt(3, inform.getId());

      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtil.closeConn(null, stmt, conn);
    }
  }



  public List<Inform> queryByPage(int currPage,int pageSize){

    String sql = "";
    if (pageSize < 0) {
      sql = "select  * from inform_inf";
    }else{
      sql = "select  * from inform_inf limit ?,?";
    }

    List<Inform> informs = new ArrayList<>();


    Connection conn = JdbcUtil.getConn();

    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      if (pageSize > 0) {
        int start = (currPage - 1) * pageSize;
        stmt.setInt(1, start);
        stmt.setInt(2, pageSize);
      }

      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {
        Inform modelResult = getModelResult(rs);
        informs.add(modelResult);
      }
      return informs;
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtil.closeConn(null, stmt, conn);
    }
    return new ArrayList<>();
  }

  public Inform queryById(int id) {

    Inform inform = new Inform();

    Connection conn = JdbcUtil.getConn();

    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.prepareStatement("select * from inform_inf where id = ?");
      stmt.setInt(1, id);

       rs = stmt.executeQuery();

      if (rs.next()) {
        Inform modelResult = getModelResult(rs);
        return modelResult;
      }

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtil.closeConn(rs, stmt, conn);
    }
    return new Inform();
  }

  public List<Inform> queryByKey(Map<String,String> map) {

    if (map == null) {
      return new ArrayList<>();
    }
    StringBuffer sb = new StringBuffer();
    map.forEach((k,v)->{
      if (k != null && v != null) {
        sb.append(" ");
        sb.append(k);
        sb.append(" ");
        sb.append("like");
        sb.append(" '%");
        sb.append(v);
        sb.append("%' ");
        sb.append("and");
        sb.append(" ");
      }

    });
    sb.delete(sb.lastIndexOf("and"), sb.length());

    List<Inform> informs = new ArrayList<>();
    Connection conn = JdbcUtil.getConn();


    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.prepareStatement("select * from inform_inf where" + sb.toString());
      rs = stmt.executeQuery();

      while (rs.next()) {
        Inform modelResult = getModelResult(rs);
        informs.add(modelResult);
      }

      return informs;
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtil.closeConn(rs, stmt, conn);
    }
    return informs;
  }


  private Inform getModelResult(ResultSet rs) throws SQLException {

    Inform inform = new Inform();

    int id = rs.getInt("id");
    String title = rs.getString("title");
    String content = rs.getString("content");
    Date gmt_created = rs.getDate("gmt_created");
    int creator_id = rs.getInt("creator_id");

    inform.setId(id);
    inform.setContent(content);
    inform.setCreatorId(creator_id);
    inform.setGmtCreated(gmt_created);
    inform.setTitle(title);

    return inform;
  }
}
